package com.zxy.ziems.server.statistics.controller;

import com.zxy.ziems.server.file.service.FileService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@RestController
public class TestEmsExcelImport {

    @Autowired
    private FileService fileService;

    @PostMapping("/test/tableHeards")
    public void tableHeards(@RequestBody List<Map<String,String>> list){
        Map<String, String> map = list.get(0);
        System.out.println(map.get("date"));
    }

    @PostMapping("/import/excel")
    public void importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        Row row04 = sheet.getRow(5);
        int lastCellNum = row04.getLastCellNum();
        for (short i = 0; i < lastCellNum; i++) {
            CellRangeAddress mergedRegion = getMergedRegion(sheet, 4, i);
            if (mergedRegion != null) {
                int firstRow = mergedRegion.getFirstRow();
                int firstColumn = mergedRegion.getFirstColumn();
                Cell firstCell = sheet.getRow(firstRow).getCell(firstColumn);
                System.out.println("Merged cell data: " + getCellValue(firstCell));
            } else {
                System.out.println("Cell data: " + getCellValue(row04.getCell(i)));
            }
//            if (Optional.ofNullable(row04.getCell(i)).isPresent()) {
//                Cell cell = row04.getCell(i);
//                if (null != cell) {
//                    CellType cellType = cell.getCellType();
//                    switch (cellType) {
//                        case STRING:
//                            System.out.println(cell.getStringCellValue());
//                            break;
//                        case NUMERIC:
//                            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
//                            SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");
//                            String formattedDate = sdf.format(date);
//                            System.out.println(formattedDate);
//                            break;
//                        case BOOLEAN:
//                            System.out.println(cell.getBooleanCellValue());
//                            break;
//                        case FORMULA:
//                            System.out.println(cell.getCellFormula());
//                            break;
//                    }
//                } else {
//                    System.out.println("null");
//                }
//            }
        }
    }

    private static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, int colNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(rowNum, colNum)) {
                return mergedRegion;
            }
        }
        return null;
    }

    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");
                    String formattedDate = sdf.format(date);
                    return formattedDate;
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return "";
            default:
                return "error";
        }
    }
}
